﻿---4. GUARANTEE  AND VOLUME

				--4.1. BAO LANH

declare @date_update nvarchar(100)= (select [DATE_UPDATE] from [DATE_EM_REPORT])
declare @date_end_month nvarchar(100)= (select [MAX_DATE_ENDMONTH] from [DATE_EM_REPORT])
declare @month nvarchar(100)=(select [MAX_MONTH] from [DATE_EM_REPORT])
DECLARE @STARTOFMONTH NVARCHAR (20)= convert (nvarchar(8),(DATEADD(M,DATEDIFF(MONTH, 0, @date_update),0)),112)

DECLARE @DATE NVARCHAR (6) 
SET @DATE=@date_update
--print (@date) 


DECLARE @DATE_NS NVARCHAR (20)
SET @DATE_NS=@month ----------BANG NHAN SU CO MUON NEN UPDATE THANG TRUOC

DECLARE @DATE0 NVARCHAR (20)
SET @DATE0 = @STARTOFMONTH

	DECLARE @DATE1 NVARCHAR (20)
	SET @DATE1=@date_update

EXEC('
IF EXISTS
	(SELECT *
		FROM dbo.sysobjects o
	WHERE o.xtype IN (''U'') AND o.name = ''LIMIT_ORG_1'')
	DROP TABLE LIMIT_ORG_1
IF EXISTS
	(SELECT *
		FROM dbo.sysobjects o
	WHERE o.xtype IN (''U'') AND o.name = ''LIMIT_ORG_3'')
	DROP TABLE LIMIT_ORG_3
')
EXEC ( '
IF EXISTS
	(SELECT *
		FROM dbo.sysobjects o
	WHERE o.xtype IN (''U'') AND o.name = ''MD_A'')
DROP TABLE MD_A')
EXEC ( '
IF EXISTS
	(SELECT *
		FROM dbo.sysobjects o
	WHERE o.xtype IN (''U'') AND o.name = ''MD_B'')
	DROP TABLE MD_B')
EXEC ( '
IF EXISTS
	(SELECT *
		FROM dbo.sysobjects o
	WHERE o.xtype IN (''U'') AND o.name = ''MD_C'')
	DROP TABLE MD_C')
EXEC ( '
IF EXISTS
	(SELECT *
		FROM dbo.sysobjects o
	WHERE o.xtype IN (''U'') AND o.name = ''DOANH_SO_BAO_LANH_'+@DATE+'_NEW '')
	DROP TABLE DOANH_SO_BAO_LANH_'+@DATE+'_NEW ')
EXEC ('
IF EXISTS
	(SELECT *
		FROM dbo.sysobjects o
	WHERE o.xtype IN (''U'') AND o.name = ''MD_D'')
	DROP TABLE MD_D')
EXEC ('
IF EXISTS
	(SELECT *
		FROM dbo.sysobjects o
	WHERE o.xtype IN (''U'') AND o.name = ''MD_FINAL'')
	DROP TABLE MD_FINAL')
EXEC ('
IF EXISTS
	(SELECT *
		FROM dbo.sysobjects o
	WHERE o.xtype IN (''U'') AND o.name = ''MD_FINAL_1'')
	DROP TABLE MD_FINAL_1')

--SELECT * FROM [ANHCP].[MBO_SBO].[dbo].[HR_file_201601]
print('1')
EXEC ('SELECT  BUSINESS_DATE,MD_ID,CUST_ID,VALUE_DATE,MUC_DICH,CUS_SEGMENT,ORG_AMOUNT*MID_RATE AS AMOUNT_QD,
        C.DAO AS DAO_CIF,D.DAO_NAME,BRANCH_NAME_SME,ZONE_ID_SME,CO_CODE,CATEGORY,FUND_PROGRAM INTO MD_A
  FROM SERVER16.[VPB_WHR2].[DBO].[MD_RPT_DAILY_VPB] A
  LEFT JOIN  SERVER16.VPB_WHR2.DBO.FOCURR_SAVE B
  ON A.CCY=B.CODE
  LEFT JOIN SERVER74.BICDATA.DBO.CUSTOMER C
  ON A.CUST_ID=C.RECID
  --LEFT JOIN TRANGLT9.DATA.DBO.HR_SME_'+@DATE_NS+' D
  LEFT JOIN TBL_HR D
  ON C.DAO COLLATE DATABASE_DEFAULT =D.DAO 
  LEFT JOIN SERVER74.BICDATA.DBO.BRANCH_CODE E
  ON A.CO_CODE=E.BRANCH_ID
WHERE SAVE_DATE='''+@DATE1+''' 
  AND CATEGORY IN (''28103'',''28104'',''28113'',''28116'',''28109'',''28114'')
  AND VALUE_DATE BETWEEN '''+@DATE0+''' AND '''+@DATE1+''' 
  		AND A.CO_CODE NOT IN (SELECT BRANCH_CODE FROM PhuongAnh.dbo.BRANCHOFF where branch_code <> ''VN0010289'')
	AND CUS_SEGMENT not in (''KHCN'',''HH'') ')
	--select * from TBL_HR
print('2')				 --4.2.BAO LANH THANH TOAN
EXEC ('SELECT BUSINESS_DATE,MD_ID,CUST_ID,VALUE_DATE,MUC_DICH,CUS_SEGMENT,ORG_AMOUNT*MID_RATE AS AMOUNT_QD,
C.DAO AS DAO_CIF,D.DAO_NAME,BRANCH_NAME_SME,ZONE_ID_SME,CO_CODE,CATEGORY,FUND_PROGRAM INTO MD_B
  FROM SERVER16.[VPB_WHR2].[DBO].[MD_RPT_DAILY_VPB] A
  LEFT JOIN  SERVER16.VPB_WHR2.DBO.FOCURR_SAVE B
  ON A.CCY=B.CODE
  LEFT JOIN SERVER74.BICDATA.DBO.CUSTOMER C
  ON A.CUST_ID=C.RECID
  --LEFT JOIN TRANGLT9.DATA.DBO.HR_SME_'+@DATE_NS+' D
  LEFT JOIN TBL_HR D
  ON C.DAO  COLLATE DATABASE_DEFAULT =D.DAO
  LEFT JOIN SERVER74.BICDATA.DBO.BRANCH_CODE E
  ON A.CO_CODE=E.BRANCH_ID
WHERE SAVE_DATE='''+@DATE1+''' 
  AND CATEGORY IN ( ''28102'')
  AND A.FUND_PROGRAM NOT LIKE ''%TAI TRO CHUOI HTC%'' AND A.FUND_PROGRAM NOT LIKE ''%VEAM''
  AND VALUE_DATE BETWEEN '''+@DATE0+''' AND '''+@DATE1+''' 
    		AND A.CO_CODE NOT IN (SELECT BRANCH_CODE FROM PhuongAnh.dbo.BRANCHOFF where branch_code <> ''VN0010289'')
	AND CUS_SEGMENT not in (''KHCN'',''HH'') ')

print('3')				 --4.3.BAO LANH 
  EXEC ('SELECT  BUSINESS_DATE,MD_ID,CUST_ID,VALUE_DATE,MUC_DICH,CUS_SEGMENT,ORG_AMOUNT*MID_RATE AS AMOUNT_QD,
  C.DAO AS DAO_CIF,D.DAO_NAME,BRANCH_NAME_SME,ZONE_ID_SME,CO_CODE,CATEGORY,FUND_PROGRAM INTO MD_C
  FROM SERVER16.[VPB_WHR2].[DBO].[MD_RPT_DAILY_VPB] A
  LEFT JOIN  SERVER16.VPB_WHR2.DBO.FOCURR_SAVE B
  ON A.CCY=B.CODE
  LEFT JOIN SERVER74.BICDATA.DBO.CUSTOMER C
  ON A.CUST_ID=C.RECID
  --LEFT JOIN TRANGLT9.DATA.DBO.HR_SME_'+@DATE_NS+' D -------- THAY BANG
  LEFT JOIN TBL_HR D
  ON C.DAO COLLATE DATABASE_DEFAULT =D.DAO 
  LEFT JOIN SERVER74.BICDATA.DBO.BRANCH_CODE E
  ON A.CO_CODE=E.BRANCH_ID
WHERE SAVE_DATE='''+@DATE1+'''   
  AND CATEGORY IN (''28609'')
  AND (A.FUND_PROGRAM LIKE ''%TAI TRO CHUOI HTC%'' or A.FUND_PROGRAM  LIKE ''%VEAM'')
  AND VALUE_DATE BETWEEN '''+@DATE0+''' AND '''+@DATE1+''' 
      		AND A.CO_CODE NOT IN (SELECT BRANCH_CODE FROM PhuongAnh.dbo.BRANCHOFF where branch_code <> ''VN0010289'')
	AND CUS_SEGMENT not in (''KHCN'',''HH'') ')


 print('4')
 EXEC ('SELECT * INTO DOANH_SO_BAO_LANH_'+@DATE+'_NEW FROM ( SELECT * FROM MD_A
	UNION
	SELECT * FROM MD_B
	UNION 
 SELECT * FROM MD_C) D ') 
 print('5')
EXEC ('SELECT * ,ROW_NUMBER() OVER( PARTITION BY MD_ID ORDER BY BUSINESS_DATE) AS RN
INTO MD_D
FROM DOANH_SO_BAO_LANH_'+@DATE+'_NEW
ORDER BY BUSINESS_DATE')
print('6')
EXEC ('SELECT [BUSINESS_DATE]
      ,[MD_ID]
      ,[CUST_ID]
      ,[VALUE_DATE]
      ,[MUC_DICH]
      ,[CUS_SEGMENT]
      ,[AMOUNT_QD] AS AMOUNT_QD
      ,[DAO_CIF]
      ,[DAO_NAME]
      ,[BRANCH_NAME_SME]
      ,[ZONE_ID_SME]
      ,[CO_CODE]
      ,[CATEGORY]
      ,[FUND_PROGRAM]
      ,[RN] 
	   INTO MD_FINAL
FROM MD_D
WHERE RN=1 ')
print('7')
EXEC ('UPDATE MD_FINAL
SET BUSINESS_DATE='''+@DATE1+''' ')

--SELECT * FROM #MD_FIN
--WHERE  FUND_PROGRAM<>'NONE'

--EXEC ('SELECT * INTO DOANH_SO_BAO_LANH FROM MD_FINAL')
print('8')
EXEC('SELECT *,ROW_NUMBER() OVER (PARTITION BY CUST_ID ORDER BY BUSINESS_DATE) AS COUNT_CIF 
INTO MD_FINAL_1
 FROM MD_FINAL')
print('9')
EXEC('UPDATE MD_FINAL_1
 SET COUNT_CIF=0
 WHERE COUNT_CIF>1')

--EXEC ('SELECT  *
--	  INTO DOANH_SO_BAO_LANH  
--	  FROM [TRADE].[dbo].MD_FINAL_1')
print('10')
EXEC('UPDATE MD_FINAL_1
SET ZONE_ID_SME=''CHUA PHAN BO''
WHERE  ZONE_ID_SME IS NULL OR ZONE_ID_SME =''NULL'' OR ZONE_ID_SME ='' ''')

--EXEC ('
----INSERT DOANH_SO_BAO_LANH
--SELECT *, BUSINESS_DATE AS TO_DATE 
--INTO DOANH_SO_BAO_LANH
--FROM MD_FINAL_1')
print('11')
EXEC ('
drop table DOANH_SO_BAO_LANH

--INSERT DOANH_SO_BAO_LANH
SELECT *, BUSINESS_DATE AS TO_DATE , left(VALUE_DATE, 7) as MONTH
INTO DOANH_SO_BAO_LANH
 FROM MD_FINAL_1 A
left join [SERVER74].[BICDATA].[dbo].[CUSTOMER] b ON A.CUST_ID=B.RECID')


--drop table DOANH_SO_BAO_LANH
--insert SERVER12.TRANGLT9.DBO.DOANH_SO_BAO_LANH
--select a.*, left(VALUE_DATE, 7) as MONTH, B.CUS_NAME
--from DOANH_SO_BAO_LANH a
--left join [SERVER74].[BICDATA].[dbo].[CUSTOMER] b ON A.CUST_ID=B.RECID
-- where value_date between '20160601' and '20160630'

--select  top 3 * from DOANH_SO_BAO_LANH
--select top 3 *  from SERVER12.TRANGLT9.DBO.DOANH_SO_BAO_LANH
--select max(from_date) from SERVER12.TRANGLT9.DBO.DOANH_SO_BAO_LANH where from_date='20160229' order by from_date 
-------=========================****************************** HẠN MỨC BẢO LÃNH
--print('12')
--EXEC ('SELECT *,
--(B.MID_RATE* 
--(CASE WHEN [INTERNAL_AMOUNT] LIKE''%B%''THEN REPLACE([INTERNAL_AMOUNT],''B'',''000000000'')
--WHEN INTERNAL_AMOUNT LIKE''%M%''THEN REPLACE([INTERNAL_AMOUNT],''M'',''000000'') 
--ELSE [INTERNAL_AMOUNT] END))/1E9 AS [INTERNAL_AMOUNT_QD],
--REVOLVING = CASE WHEN ID LIKE''%.0050000%'' THEN ''Y''
--WHEN ID LIKE ''%.0060000%'' THEN ''N'' END
--      INTO LIMIT_ORG_1
--  FROM SERVER16.[VPB_WHR2].[DBO].[TBL_LIMIT_ORG] A
--  LEFT JOIN [SERVER16].[VPB_WHR2].DBO.FOCURR_SAVE B
--ON A.LIMIT_CURRENCY=B.CODE 
----AND A.BUSINESS_DATE=B.SAVE_DATE 
--AND B.SAVE_DATE ='''+@DATE1+'''
--  WHERE BUSINESS_DATE ='''+@DATE1+''' AND
--     LIABILITY_NUMBER IN (SELECT DISTINCT CUST_ID FROM MD_FINAL_1)
--  AND (ID LIKE''%.0050000%'' OR ID LIKE ''%.0060000%'') ')

--  --BUSINESS_DATE='2015-12-31'  --THAY NGAY-- AND 
--  --LIABILITY_NUMBER IN (SELECT DISTINCT CUST_ID FROM SERVER12.TRANGLT9.DBO.DOANH_SO_BAO_LANH )

--  print('13')
--EXEC('SELECT DISTINCT CUST_ID, A.BUSINESS_DATE, 
--C.CUS_NAME, BRANCH_NAME_SME, ZONE_ID_SME, FUND_PROGRAM=''HAN MUC BAO LANH'', 
--[INTERNAL_AMOUNT_QD] AS LIMIT_AMOUNT, REVOLVING into LIMIT_ORG_3
--FROM MD_FINAL_1 A
----FROM SERVER12.TRANGLT9.DBO.DOANH_SO_BAO_LANH A
--LEFT JOIN (SELECT LIABILITY_NUMBER, REVOLVING, BUSINESS_DATE, SUM([INTERNAL_AMOUNT_QD]) AS [INTERNAL_AMOUNT_QD] 
--FROM LIMIT_ORG_1 GROUP BY LIABILITY_NUMBER, REVOLVING, BUSINESS_DATE) B
--ON A.CUST_ID=B.LIABILITY_NUMBER AND A.BUSINESS_DATE=B.BUSINESS_DATE
--LEFT JOIN [SERVER74].[BICDATA].[dbo].[CUSTOMER] C
--ON A.CUST_ID=C.RECID')

--print('14')
--EXEC('
----INSERT [LIMIT_ORG_BAO_LANH_CHUNG] 
--SELECT *, BUSINESS_DATE AS TO_DATE 
--into LIMIT_ORG_BAO_LANH_CHUNG
--FROM LIMIT_ORG_3
-- where limit_amount is not null')


--SELECT *  FROM SERVER12.ANHCP.[DBO].[LIMIT_ORG_BAO_LANH_CHUNG] 
--DROP TABLE DOANH_SO_BAO_LANH_201602_NEW

--DELETE FROM DOANH_SO_BAO_LANH
--delete from server12.tranglt9.dbo.DOANH_SO_BAO_LANH
--SELECT * FROM server12.tranglt9.dbo.DOANH_SO_BAO_LANH
--WHERE BUSINESS_DATE='2015-10-31'
--SELECT * FROM DOANH_SO_BAO_LANH
--delete server12.tranglt9.dbo.DOANH_SO_BAO_LANH
--where business_date='2015-11-30'


------------+++++++++++++++ Các sản phẩm bảo lãnh
----Nhóm bảo lãnh phát hành
--28102: bảo lãnh thanh toán
--28103: bảo lãnh thực hiện hợp đồng
--28104: Bảo lãnh dự thầu
--28109: bảo lãnh bảo hành
--28110: Bảo lãnh đấu giá
--28111: Tái bảo lãnh
--28199: bảo lãnh khác
---- nhóm bảo lãnh nhận
--28301: Bảo lãnh đảm bảo tín dụng
--28319: Bảo lãnh nhận khác